Release 10.1A: OpenEdge Data Management:
SQL Development

Setting isolation levels

The degree to which one transaction can interfere with other transactions by accessing the same rows concurrently is determined by setting the transaction isolation level in the program.

This is the syntax for the SET TRANSACTION ISOLATION LEVEL statement:

Syntax

SET TRANSACTION ISOLATION LEVEL isolation_level_name ;

isolation_level_name

Syntax

READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

SQL defines isolation levels in terms of the inconsistencies they allow:

READ UNCOMMITTED

Also called a “dirty read.” When this isolation level is used, a transaction can read uncommitted data that later can be rolled back. A transaction that uses this isolation level can only fetch data but cannot update, delete, or insert data.

READ COMMITTED

With this isolation level dirty reads are not possible, but if the same row is read repeatedly during the same transaction, its contents can be changed or the entire row can be deleted by other transactions.

REPEATABLE READ

This isolation level guarantees that a transaction can read the same row many times, and it will remain intact. However, if a query with the same search criteria (the same WHERE clause) is executed more than once, each execution can return different sets of rows. This can happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria.

SERIALIZABLE

This isolation level guarantees that none of the above happens. In addition, it guarantees that transactions that use this level will be completely isolated from other transactions.

Table 8–1 identifies which phenomena are either permitted or prevented by each isolation level.

Table 8–1: Transaction isolation levels

Isolation	Dirty read	Nonrepeatable read	Phantom read
READ UNCOMMITTED	Permitted	Permitted	Permitted
READ COMMITTED	Prevented	Permitted	Permitted
REPEATABLE READ	Prevented	Prevented	Permitted
SERIALIZABLE	Prevented	Prevented	Prevented

Notes: The isolation levels are ordered according to the phenomena they either permit or prevent.

The first one, READ UNCOMMITTED, is the isolation level providing the highest level of concurrency but with the lowest level of consistency.

Each subsequent level provides at least as much data consistency as the one before but will result in less concurrency.

As a general rule, the more data consistency that is provided by the isolation level used from an application, the less concurrency is allowed between this application and other applications connected to the same database.